package sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import logic.AdminHandler;
import logic.CorrelationRecAlgorithm;
import logic.Facade;
import logic.SystemHandler;
import logic.UserHandler;
import data.MovieTuple;
import data.RatingTuple;
import data.SystemData;
import data.SystemXmlDataBase;
import data.UserTuple;
import exceptions.ObjectAlreadyInitializedException;

public class XmlToSql {
	public static void main(String[] args){
		try {
			Facade.init(new SystemHandler(), new AdminHandler() , new UserHandler(),new CorrelationRecAlgorithm());
			SystemData.doXML();
			Statement stmt;
		
			//Register the JDBC driver for MySQL.
			//Class.forName("com.mysql.jdbc.Driver");
			
			//Define URL of database server for
			// database named mysql on the localhost
			// with the default port number 3306.
			String url = "jdbc:mysql://localhost:3306/movies_db";
		
			//Get a connection to the database for a
			// user named root with a blank password.
			// This user is the default administrator
			// having full privileges to do anything.
			Connection con = DriverManager.getConnection(url,"admin", "dog");
		
			//Display URL and connection information
			System.out.println("URL: " + url);
			System.out.println("Connection: " + con);
		
			//Get a Statement object
			stmt = con.createStatement();
			String createCmd;
			createCmd = "CREATE TABLE users(name VARCHAR(50) PRIMARY KEY, "+
				  						   "password CHAR(40), "+
				  						   "bdate VARCHAR(10), "+
				  						   "sex VARCHAR(5), "+
				  						   "type VARCHAR(13));";
			stmt.executeUpdate(createCmd);
			System.out.println(createCmd);
			createCmd = "CREATE TABLE movies(name VARCHAR(100) PRIMARY KEY, "+
			                                "category VARCHAR(50), "+
			                                "year CHAR(4), "+
			                                "director VARCHAR(50), "+
			                                "picture VARCHAR(100), "+
			                                "rating VARCHAR(5), "+
			                                "raters VARCHAR(6), "+
			                                "language VARCHAR(20), "+
			                                "length VARCHAR(3));";
			stmt.executeUpdate(createCmd);
			System.out.println(createCmd);
			createCmd = "CREATE TABLE ratings(user VARCHAR(50), "+
					   						 "movie VARCHAR(100), "+
					   						 "rating CHAR(1), "+
					   						 "date VARCHAR(50), "+
					   						 "description VARCHAR(300), " +
					   						 "Primary Key(user,movie), " +
					   						 "FOREIGN KEY (user) REFERENCES users(name) ON DELETE cascade, " +
					   						 "FOREIGN KEY (movie) REFERENCES movies(name) ON DELETE cascade);";
			stmt.executeUpdate(createCmd);
			System.out.println(createCmd);
			for (UserTuple user : SystemXmlDataBase.getInstance().getAllUsers()){
				String mask = "INSERT INTO users(name, password, bdate, sex, type) VALUES(";
				String cmd =       mask +"'"+ user.get_name() +"', '"+
											  user.get_password() + "', '" + 
											  user.get_bdate() + "', '" + 
											  user.is_sex()+"', '"+ 
											  user.get_type() +"');";
				stmt.executeUpdate(cmd);
				System.out.println(cmd);
	    	}
	    	//adds all the movies to the movie list
	    	for (MovieTuple movie : SystemXmlDataBase.getInstance().getAllMovies()){
	    		String mask = "INSERT INTO movies(name, category, year, director, picture, rating, raters, language, length) VALUES(";
				String cmd =       mask +"'"+ movie.get_name() +"', '"+
											  movie.get_category() + "', '" + 
											  movie.get_year() + "', '" + 
											  movie.get_director() +"', '"+
											  movie.get_picture() + "', '" + 
											  movie.get_rating() +"', '"+
											  movie.get_raters() + "', '" + 
											  movie.get_language() +"', '"+
											  movie.get_length() +"');";
				stmt.executeUpdate(cmd);
				System.out.println(cmd);
	    	}
	    	//adds all the recommendation to the recommendation list
	    	for (RatingTuple rec : ((SystemXmlDataBase)SystemData.getInstance()).get_rec().values()){
	    		String mask = "INSERT INTO ratings(user, movie, rating, date, description) VALUES(";
				String cmd =       mask +"'"+ rec.get_user().get_name() +"', '"+
											  rec.get_movie().get_name() + "', '" + 
											  rec.get_rating() + "', '" +
											  rec.get_date() + "', '" +
											  rec.get_description() +"');";
				stmt.executeUpdate(cmd);
				System.out.println(cmd);
	    	}
	    	System.out.println("DONE!!");
		} catch (ObjectAlreadyInitializedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
